# -*- coding: UTF-8 -*-

import json
import time

import flask_excel
import pyexcel
from flask import Flask, request
from flask import jsonify
from flask_migrate import Migrate
from flask_paginate import Pagination, get_page_args
from flask_sqlalchemy import SQLAlchemy
from flask_cors import CORS

from flask_siwadoc import SiwaDoc
from pydantic import BaseModel, Field

import mysql_operate

from json_response import JsonResponse, StaffTable
from flask_sqlalchemy_config import BaseConfig

app = Flask(__name__)
# 初始化生成一个app对象，这个对象就是Flask的当前实例对象，后面的各个方法调用都是这个实例
# Flask会进行一系列自己的初始化，比如web API路径初始化，web资源加载，日志模块创建等。然后返回这个创建好的对象给你
# flask接口全局跨域设置
# CORS(app, origins='http://127.0.0.1:10029')
CORS(app)

# flask集成接口文档
siwa = SiwaDoc(app, version='1.0', title="My Api",
               description="SwaggerUI 船舶配员数据可视化的后端数据接口文档，通过Flask-siwadoc实现")

app.config.from_object(BaseConfig)
db = SQLAlchemy(app)
# migrate = Migrate(app, db)


class DeckDepartment(db.Model):
    __tablename__ = 'deck_department'

    id = db.Column('id', db.Integer, primary_key=True)
    department = db.Column('department', db.String(30))
    ship_type = db.Column('ship_type', db.String(30))
    navigation_zone = db.Column('navigation_zone', db.String(30))
    tonnage = db.Column('tonnage', db.String(30))
    stipulate = db.Column('stipulate', db.String(30))
    captain = db.Column('captain', db.Integer)
    first_mate = db.Column('first_mate', db.Integer)
    second_mate = db.Column('second_mate', db.Integer)
    third_mate = db.Column('third_mate', db.Integer)
    senior_duty_sailor = db.Column('senior_duty_sailor', db.Integer)
    duty_sailor = db.Column('duty_sailor', db.Integer)
    pilot = db.Column('pilot', db.Integer)
    offline = db.Column('offline', db.Integer)
    upper_limit = db.Column('upper_limit', db.Integer)


class EngineerDepartment(db.Model):
    __tablename__ = 'engineer_department'

    id = db.Column('id', db.Integer, primary_key=True)
    department = db.Column('department', db.String(30))
    ship_type = db.Column('ship_type', db.String(30))
    navigation_zone = db.Column('navigation_zone', db.String(30))
    total_power = db.Column('total_power', db.String(30))
    stipulate = db.Column('stipulate', db.String(30))
    chief_engineer = db.Column('chief_engineer', db.Integer)
    first_engineer = db.Column('first_engineer', db.Integer)
    second_engineer = db.Column('second_engineer', db.Integer)
    third_engineer = db.Column('third_engineer', db.Integer)
    senior_duty_mechanic = db.Column('senior_duty_mechanic', db.Integer)
    duty_mechanic = db.Column('duty_mechanic', db.Integer)
    marine_engineer = db.Column('marine_engineer', db.Integer)
    offline = db.Column('offline', db.Integer)
    upper_limit = db.Column('upper_limit', db.Integer)


class CrewJob(db.Model):
    __tablename__ = 'crew_job'

    id = db.Column('id', db.String(100), primary_key=True)
    shipdutycode = db.Column('shipdutycode', db.String(100))
    mmsi = db.Column('mmsi', db.String(100))
    checkPass = db.Column('checkPass', db.String(100))
    shiptype = db.Column('shiptype', db.String(100))
    captain = db.Column('captain', db.String(100))
    dismissalstatus = db.Column('dismissalstatus', db.String(100))
    certno = db.Column('certno', db.String(100))
    crewcradno = db.Column('crewcradno', db.String(100))
    cardid = db.Column('cardid', db.String(100))
    crewname = db.Column('crewname', db.String(100))
    navigationarea = db.Column('navigationarea', db.String(100))
    lat = db.Column('lat', db.String(100))
    portcode = db.Column('portcode', db.String(100))
    dismissaltime = db.Column('dismissaltime', db.String(100))
    dutytime = db.Column('dutytime', db.String(100))
    lng = db.Column('lng', db.String(100))
    shipduty = db.Column('shipduty', db.String(100))
    shiplevel = db.Column('shiplevel', db.String(100))
    shiplevelcode = db.Column('shiplevelcode', db.String(100))
    port = db.Column('port', db.String(100))
    shipname = db.Column('shipname', db.String(100))
    shipid = db.Column('shipid', db.String(100))
    status = db.Column('status', db.String(100))
    internship = db.Column('internship', db.String(100))


class CrewInfo(db.Model):
    __tablename__ = 'crew_info'

    id = db.Column('id', db.Integer, primary_key=True)
    name = db.Column('name', db.String(50))
    gender = db.Column('gender', db.String(50))
    hometown = db.Column('hometown', db.String(50))
    judge = db.Column('judge', db.Boolean)
    birthday = db.Column('birthday', db.String(20))
    post = db.Column('post', db.String(50))
    experience = db.Column('experience', db.String(200))


class ShipInfo(db.Model):
    __tablename__ = "ship_info"

    ship_id = db.Column("ship_id", db.String(50), primary_key=True)
    ship_name = db.Column("ship_name", db.String(50))
    ship_registration_number = db.Column("ship_registration_number", db.String(50))
    port_of_registry = db.Column("port_of_registry", db.String(50))
    call_sign = db.Column("call_sign", db.String(50))
    ship_type = db.Column("ship_type", db.String(50))
    tonnage = db.Column("tonnage", db.String(50))
    total_power = db.Column('total_power', db.String(50))
    degree_of_cabin_automation = db.Column('degree_of_cabin_automation', db.String(50))
    passenger_capacity_quota = db.Column('passenger_capacity_quota', db.String(50))
    navigation_zone = db.Column('navigation_zone', db.String(50))
    captain = db.Column('captain', db.Integer)
    first_mate = db.Column('first_mate', db.Integer)
    second_mate = db.Column('second_mate', db.Integer)
    third_mate = db.Column('third_mate', db.Integer)
    pilot = db.Column('pilot', db.Integer)
    senior_duty_sailor = db.Column('senior_duty_sailor', db.Integer)
    duty_sailor = db.Column('duty_sailor', db.Integer)
    chief_engineer = db.Column('chief_engineer', db.Integer)
    first_engineer = db.Column('first_engineer', db.Integer)
    second_engineer = db.Column('second_engineer', db.Integer)
    third_engineer = db.Column('third_engineer', db.Integer)
    marine_engineer = db.Column('marine_engineer', db.Integer)
    senior_duty_mechanic = db.Column('senior_duty_mechanic', db.Integer)
    duty_mechanic = db.Column('duty_mechanic', db.Integer)
    passenger_transport_department_personnel = db.Column('passenger_transport_department_personnel', db.Integer)
    part_time_GMDSS_restricted_operator = db.Column('part_time_GMDSS_restricted_operator', db.Integer)
    GMDSS_No = db.Column('GMDSS_No', db.Integer)
    electronic_electrical_technician = db.Column('electronic_electrical_technician', db.Integer)
    electronics_technician = db.Column('electronics_technician', db.Integer)
    full_time_radio_electronics_officer = db.Column('full_time_radio_electronics_officer', db.Integer)


@app.route("/")
@siwa.doc()
def get_index():
    return '关于船舶配员数据可视化————flask后端程序'


@app.route("/crew_job/count")
@siwa.doc(tags=['船员任职'])
def get_crew_count():
    return jsonify(CrewJob.query.count())


@app.route("/crew_job/pagination_query")
@siwa.doc(tags=['船员任职'])
def get_crew_job_table_page_query():
    page, per_page, offset = get_page_args(page_parameter='page', per_page_parameter='per_page')
    inputKeyword = request.args.get('inputKeyword')
    crew_job = CrewJob.query.filter(
        CrewJob.crewname.like(f'%{inputKeyword}%') | CrewJob.shipname.like(f'%{inputKeyword}%') | CrewJob.shipduty.like(
            f'%{inputKeyword}%')).offset(
        offset).limit(
        per_page).all()

    pagination = Pagination(page=page, total=CrewJob.query.filter(
        CrewJob.crewname.like(f'%{inputKeyword}%') | CrewJob.shipname.like(f'%{inputKeyword}%') | CrewJob.shipduty.like(
            f'%{inputKeyword}%')).count(), per_page=per_page)
    return jsonify({
        'data': [{'id': info.id, 'mmsi': info.mmsi, 'crewname': info.crewname, 'shipname': info.shipname,
                  'shipid': info.shipid, 'shiptype': info.shiptype, 'shipduty': info.shipduty, 'cardid': info.cardid,
                  'dutytime': info.dutytime, 'shiplevel': info.shiplevel, 'checkPass': info.checkPass} for info in
                 crew_job],
        'pagination': {
            'page': page,
            'per_page': per_page,
            'total': pagination.total
        }
    })


@app.route("/crew_job/pagination")
@siwa.doc(tags=['船员任职'])
def get_crew_job_table_page():
    page, per_page, offset = get_page_args(page_parameter='page', per_page_parameter='per_page')
    crew_job = CrewJob.query.offset(offset).limit(per_page).all()

    pagination = Pagination(page=page, total=CrewJob.query.count(), per_page=per_page, css_framework='bootstrap4')
    return jsonify({
        'data': [{'id': info.id, 'mmsi': info.mmsi, 'crewname': info.crewname, 'shipname': info.shipname,
                  'shipid': info.shipid, 'shiptype': info.shiptype, 'shipduty': info.shipduty, 'cardid': info.cardid,
                  'dutytime': info.dutytime, 'shiplevel': info.shiplevel, 'checkPass': info.checkPass} for info in
                 crew_job],
        'pagination': {
            'page': page,
            'per_page': per_page,
            'total': pagination.total
        }
    })


@app.route("/crew_job/timeline")
@siwa.doc(tags=['船员任职'])
def get_crew_job_timeline_data():
    card_id = request.args.get('cardid')
    sql = "select crewname , shipname,shiptype,shiplevel , mmsi,dutytime ,shipduty from crew_job where cardid = '" + card_id + "'  order by dutytime"
    data = mysql_operate.db.select_db(sql)
    return jsonify(data)


@app.route("/deck/all", methods=["GET"])
@siwa.doc(tags=['甲板部'])
def get_deck_all():
    """获取所有用户信息"""
    sql = "select * from deck_department"  # sql语句，可自行对应自己数据相应的表进行操作
    data = mysql_operate.db.select_db(sql)  # 用mysql_operate文件中的db的select_db方法进行查询
    print("获取所有甲板部最低配员数据 == >> {}".format(data))  # 在pycharm下打印信息
    return jsonify(data)  # 在页面输出返回信息的json格式


@app.route("/deck/query_standard_by_ship_type", methods=["GET"])
@siwa.doc(tags=['甲板部'])
def get_deck_by_ship_type():
    ship_type = request.args.get('ship_type')
    data1 = DeckDepartment.query.filter(DeckDepartment.ship_type == ship_type).with_entities(
        DeckDepartment.tonnage, DeckDepartment.stipulate, DeckDepartment.navigation_zone, DeckDepartment.id).all()
    return jsonify([data._asdict() for data in data1])


@app.route("/deck/stipulate/update", methods=['POST'])
@siwa.doc(tags=['甲板部'])
def update_deck_stipulate():
    json_data = json.loads(request.get_data().decode('utf-8'))
    id = json_data.get('id')
    tonnage = json_data.get('tonnage')
    stipulate = json_data.get('stipulate')
    data = DeckDepartment.query.filter(DeckDepartment.id == id).first()
    data.tonnage = tonnage
    data.stipulate = stipulate

    db.session.commit()
    return JsonResponse(True, "200", "修改成功").to_dict()


@app.route("/engineer/stipulate/update", methods=['POST'])
@siwa.doc(tags=['轮机部'])
def update_engineer_stipulate():
    json_data = json.loads(request.get_data().decode('utf-8'))
    id = json_data.get('id')
    total_power = json_data.get('total_power')
    stipulate = json_data.get('stipulate')
    data = EngineerDepartment.query.filter(EngineerDepartment.id == id).first()
    data.total_power = total_power
    data.stipulate = stipulate

    db.session.commit()
    return JsonResponse(True, "200", "修改成功").to_dict()


@app.route("/passenger/stipulate", methods=["GET"])
@siwa.doc(tags=['客运部'])
def get_passenger_stipulate():
    return mysql_operate.db.select_db("select * from passenger_department")


@app.route("/passenger/stipulate/update", methods=['POST'])
@siwa.doc(tags=['客运部'])
def update_passenger_stipulate():
    json_data = json.loads(request.get_data().decode('utf-8'))
    stipulate = json_data.get('stipulate')
    mysql_operate.db.execute_db("update table passenger_department set stipulate = " + stipulate)
    return JsonResponse("", "200", "修改成功").to_dict()


class TonnageModel(BaseModel):
    tonnage: str = Field(title="船舶吨位")


@app.route('/deck/query_standard', methods=['GET'])
@siwa.doc(tags=['甲板部'])
def get_deck_department_standard():
    """查询  一般轮船  客船   甲板部最低安全配员标准"""
    tonnage = int(request.args.get('tonnage'))
    ship_type = request.args.get('ship_type')

    data1 = DeckDepartment.query.filter(
        tonnage >= DeckDepartment.offline, tonnage <= DeckDepartment.upper_limit,
        DeckDepartment.ship_type == ship_type).with_entities(
        DeckDepartment.captain, DeckDepartment.first_mate, DeckDepartment.second_mate, DeckDepartment.third_mate,
        DeckDepartment.senior_duty_sailor, DeckDepartment.duty_sailor, DeckDepartment.pilot).all()
    # print(data1)
    return jsonify([data._asdict() for data in data1][0])


@app.route('/deck/Tugboat/sea', methods=['GET'])
@siwa.doc(tags=['甲板部'])
def get_deck_tug_boat_sea():
    """海上拖船最低安全配员标准"""
    # 甲板部海上拖船最低安全配员标准
    time.sleep(0.07)
    sql1 = "select captain,first_mate,second_mate,third_mate,duty_sailor,pilot from deck_department where ship_type= '拖船' and navigation_zone='海上' "
    content = mysql_operate.db.select_db(sql1)
    return jsonify({
        "over3000": content[0],
        "below3000": content[1]
    })


@app.route('/deck/Tugboat/port', methods=['GET'])
@siwa.doc(tags=['甲板部'])
def get_deck_tug_boat_within_port():
    '''海上拖船最低安全配员标准'''
    # 甲板部海上拖船最低安全配员标准
    time.sleep(0.07)
    sql1 = "select captain,first_mate,second_mate,third_mate,duty_sailor,pilot from deck_department where ship_type= '拖船' and navigation_zone='港内' "
    content = mysql_operate.db.select_db(sql1)
    return jsonify({
        "over750": content[0],
        "below750": content[1]
    })


@app.route("/engineer/all", methods=["GET"])
@siwa.doc(tags=['轮机部'])
def get_engineer_all():
    """获取所有用户信息"""
    sql = "select * from engineer_department"  # sql语句，可自行对应自己数据相应的表进行操作
    data = mysql_operate.db.select_db(sql)  # 用mysql_operate文件中的db的select_db方法进行查询
    print("获取所有轮机部最低配员数据 == >> {}".format(data))  # 在pycharm下打印信息
    return jsonify(data)  # 在页面输出返回信息的json格式


@app.route("/engineer/stipulate", methods=["GET"])
@siwa.doc(tags=['轮机部'])
def get_engineer_stipulate():
    data1 = EngineerDepartment.query.with_entities(
        EngineerDepartment.total_power, EngineerDepartment.stipulate, EngineerDepartment.navigation_zone).all()
    return jsonify([data._asdict() for data in data1])


@app.route('/engineer/standard', methods=['GET'])
@siwa.doc(tags=['轮机部'])
def get_engineer_standard():
    """轮机部  所有船舶"""
    total_power = float(request.args.get('total_power'))
    # 轮机部 海上航区 最低配员标准

    data1 = EngineerDepartment.query.filter(
        total_power >= EngineerDepartment.offline, total_power <= EngineerDepartment.upper_limit,
        EngineerDepartment.ship_type == '所有船舶').with_entities(EngineerDepartment.chief_engineer,
                                                              EngineerDepartment.first_engineer,
                                                              EngineerDepartment.second_engineer,
                                                              EngineerDepartment.third_engineer,
                                                              EngineerDepartment.senior_duty_mechanic,
                                                              EngineerDepartment.duty_mechanic,
                                                              EngineerDepartment.marine_engineer).all()
    return jsonify([data._asdict() for data in data1][0])


# class LoginModel(BaseModel):
#     username: str
#     password: str

# 用户登录验证
@app.route('/user/login', methods=['POST'])
@siwa.doc(tags=['登录注册'])
def user_login():
    # request.form.get('username')      一直获取不到前段传入的json表单数据，以下解决方法。
    json_data = json.loads(request.get_data().decode('utf-8'))
    username = json_data.get('username')
    password = json_data.get('password')
    sql = "select * from user where username = '" + username + "' and password = '" + password + "' "
    # print("打印MySQL语句\t" + sql)
    data = mysql_operate.db.select_db(sql)
    # 当Flask接口返回布尔值时，有时候会遇到一些问题，特别是在序列化响应时。Flask默认情况下不会直接序列化布尔值，因此需要进行一些额外的处理。
    # 你可以使用Flask的jsonify函数来确保正确地序列化布尔值并返回JSON格式的响应。下面是一个示例：
    if data:
        return JsonResponse(True, "200", "登录成功").to_dict()
    else:
        return JsonResponse(False, "404", "登录失败").to_dict()


# 用户注册
@app.route('/user/register', methods=['POST'])
@siwa.doc(tags=['登录注册'])
def user_register():
    username = str(request.form.get('username'))
    password = str(request.form.get('password'))

    sql1 = "select * from user WHERE username = '" + username + "'"
    username_data = mysql_operate.db.select_db(sql1)
    if username_data:
        return JsonResponse(True, "200", "注册成功").to_dict()
    else:
        sql2 = "insert into user(username,password) values('" + username + "','" + password + " ')"
        mysql_operate.db.execute_db(sql2)
        return JsonResponse(True, "404", "注册失败").to_dict()


# @app.route("/delete", methods=["GET", "POST"])
# def delete():
#     """删除信息"""
#     id = str(request.args.get('id'))
#     sql = "SELECT * FROM favorites WHERE id =" + id
#     data = mysql_operate.db.select_db(sql)
#     if data:
#         sql1 = "DELETE FROM favorites WHERE id =" + id
#         mysql_operate.db.execute_db(sql1)
#         return '删除成功'
#     else:
#         return '不存在此id'

@app.route('/staff_info/all', methods=['GET'])
@siwa.doc(tags=['配员'])
def get_staff_info_all():
    """获取船舶配员信息"""
    sql = "select captain,first_mate,second_mate,third_mate,pilot,senior_duty_sailor,duty_sailor,chief_engineer,first_engineer,second_engineer,third_engineer,marine_engineer,senior_duty_mechanic,duty_mechanic,passenger_transport_department_personnel,part_time_GMDSS_restricted_operator,GMDSS_No,electronic_electrical_technician,electronics_technician,full_time_radio_electronics_officer from ship_info limit 1"
    data = mysql_operate.db.select_db(sql)  # 用mysql_operate文件中的db的select_db方法进行查询

    return jsonify(data[0])  # 在页面输出返回信息的json格式


@app.route('/staff_info/deck/', methods=['GET'])
@siwa.doc(tags=['配员'])
def get_staff_engineer():
    ship_id = request.args.get('ship_id')
    # sql = "select captain,first_mate,second_mate,third_mate,senior_duty_sailor,duty_sailor,pilot from ship_info where ship_id= '" + ship_id + "'"
    # data = mysql_operate.db.select_db(sql)
    staff_data = ShipInfo.query.filter(ShipInfo.ship_id == ship_id).with_entities(ShipInfo.captain,
                                                                                  ShipInfo.first_mate,
                                                                                  ShipInfo.second_mate,
                                                                                  ShipInfo.third_mate,
                                                                                  ShipInfo.senior_duty_sailor,
                                                                                  ShipInfo.duty_sailor,
                                                                                  ShipInfo.pilot).all()

    return jsonify([data._asdict() for data in staff_data][0])


@app.route('/staff_info/engineer/', methods=['GET'])
def get_staff_deck():
    ship_id = request.args.get('ship_id')
    # sql = "select chief_engineer,first_engineer,second_engineer,third_engineer,senior_duty_mechanic,duty_mechanic,marine_engineer from ship_info where ship_id=  '" + ship_id + "'"
    # data = mysql_operate.db.select_db(sql)
    staff_data = ShipInfo.query.filter(ShipInfo.ship_id == ship_id).with_entities(ShipInfo.chief_engineer,
                                                                                  ShipInfo.first_engineer,
                                                                                  ShipInfo.second_engineer,
                                                                                  ShipInfo.third_engineer,
                                                                                  ShipInfo.senior_duty_mechanic,
                                                                                  ShipInfo.duty_mechanic,
                                                                                  ShipInfo.marine_engineer).all()
    return jsonify([data._asdict() for data in staff_data][0])
    # return jsonify(data[0])


@app.route("/ship_info/all", methods=['GET'])
@siwa.doc(tags=['船舶信息'])
def get_ship_info_all():
    """获取船舶信息表全量数据"""
    sql = 'select * from ship_info '
    data = mysql_operate.db.select_db(sql)
    total = mysql_operate.db.select_db('select count(*) as count from ship_info')
    return StaffTable(data, total[0]['count']).to_dict()


@app.route("/ship_info/all/total", methods=['GET'])
def get_ship_info_all_total():
    """获取船舶配员表全部信息的数据条数"""
    sql = 'select count(*) from ship_info '
    data = mysql_operate.db.select_db(sql)
    return jsonify(data)


class PageQueryModel01(BaseModel):
    inputKey: str = Field(title="输入关键词")
    pageNum: int = Field(title="页码")
    pageSize: int = Field(title="每页数目")


@app.route('/ship_info/query_keyword', methods=['GET'])
# @siwa.doc(query=PageQueryModel01, tags=['ship'])
def query_ship_info_by_keyword():
    """船名  模糊查询  分页 船舶信息"""
    inputKey = str(request.args.get('inputKey'))
    pageNum = request.args.get('pageNum', type=int)
    pageSize = request.args.get('pageSize', type=int)
    start = (pageNum - 1) * pageSize
    # end = start + pageSize
    sql1 = "select * from ship_info where ship_name like '%" + inputKey + "%' limit " + str(start) + "," + str(
        pageSize) + ""

    # sql2 = "select * from ship_info where ship_name like %" + inputKey + "% or ship_ip like %" + inputKey + "%"
    print(sql1)
    staff_data = mysql_operate.db.select_db(sql1)
    total = mysql_operate.db.select_db(
        "select count(*) as count from ship_info where ship_name like '%" + inputKey + "%' ")
    return StaffTable(staff_data, total[0]['count']).to_dict()  # 在页面输出返回信息的json格式


class ShipIDModel(BaseModel):
    ship_id: str = Field(title="船舶识别号")


@app.route('/staff_info/query_ship_id', methods=['GET'])
@siwa.doc(query=ShipIDModel, tags=['配员'])
def query_staff_info_by_id():
    time.sleep(0.3)
    """船舶识别码精准查询船舶配员信息"""
    ship_id = str(request.args.get('ship_id'))
    sql = "select captain,first_mate,second_mate,third_mate,pilot,senior_duty_sailor,duty_sailor,chief_engineer,first_engineer,second_engineer,third_engineer,marine_engineer,senior_duty_mechanic,duty_mechanic,passenger_transport_department_personnel,part_time_GMDSS_restricted_operator,GMDSS_No,electronic_electrical_technician,electronics_technician,full_time_radio_electronics_officer from ship_info where ship_id = '" + ship_id + "'"
    staff_data = mysql_operate.db.select_db(sql)
    # print("打印MySQL语句\t" + sql)
    return jsonify(staff_data[0])  # 在页面输出返回信息的json格式


@app.route('/staff_info/query_ship_id/department', methods=['GET'])
@siwa.doc(query=ShipIDModel, tags=['配员'])
def query_department_staff_info_by_id():
    """船舶识别码精准查询 部门"""
    time.sleep(0.1)
    ship_id = str(request.args.get('ship_id'))
    sql = "select (captain+first_mate+second_mate+third_mate+pilot+senior_duty_sailor+duty_sailor) as deck, (chief_engineer+first_engineer+second_engineer+third_engineer+marine_engineer+senior_duty_mechanic+duty_mechanic) as engineer,passenger_transport_department_personnel as passenger,(part_time_GMDSS_restricted_operator+GMDSS_No+electronic_electrical_technician+electronics_technician+full_time_radio_electronics_officer) as radio from ship_info where ship_id = '" + ship_id + "'"
    staff_data = mysql_operate.db.select_db(sql)
    # print("打印MySQL语句\t" + sql)
    return jsonify(staff_data[0])  # 在页面输出返回信息的json格式


@app.route("/crew_info/add", methods=['POST'])
def add_boatman():
    json_data = json.loads(request.get_data().decode('utf-8'))

    info = CrewInfo(
        name=json_data.get('name'),
        gender=json_data.get('gender'),
        hometown=json_data.get('hometown'),
        judge=json_data.get('judge'),
        birthday=json_data.get('birthday'),
        post=json_data.get('post'),
        experience=json_data.get('experience'),
    )
    db.session.add(info)
    db.session.commit()
    return JsonResponse(True, "200", "插入成功").to_dict()


@app.route('/ship_info/query_ship_id/', methods=['GET'])
@siwa.doc(query=ShipIDModel, tags=['船舶信息'])
def query_ship_info_by_id():
    """船舶识别码精准查询船舶信息"""
    ship_id = str(request.args.get('ship_id'))
    sql = "select * from ship_info where ship_id='" + ship_id + "' "
    staff_data = mysql_operate.db.select_db(sql)
    # print("打印MySQL语句\t" + sql)
    return jsonify(staff_data[0])


class PageQueryModel02(BaseModel):
    pageNum: str = Field(title="页码")
    pageSize: str = Field(title="每页数目")


@app.route('/ship_info/query_page/', methods=['GET'])
@siwa.doc(query=PageQueryModel02, tags=['船舶信息'])
def query_staff_info_page():
    """分页查询船舶信息"""
    pageNum = request.args.get("pageNum", type=int, default=1)
    pageSize = request.args.get('pageSize', type=int)
    start = (pageNum - 1) * pageSize
    end = start + pageSize
    sql = "select * from ship_info limit " + str(start) + "," + str(pageSize)
    # print(sql)

    total = mysql_operate.db.select_db("select count(*) as count from ship_info")

    content = mysql_operate.db.select_db(sql)
    return StaffTable(content, total[0]['count']).to_dict()


@app.route("/ship_info/columns_names/cn", methods=['GET'])
@siwa.doc(tags=['other'])
def get_columns_names_cn():
    """获取船舶信息表字段中文注释"""
    sql_c = "select COLUMN_NAME,COLUMN_COMMENT from information_schema.columns WHERE table_name='ship_info' and table_schema='my_db01' order by ordinal_position"
    data_list = mysql_operate.db.select_db(sql_c)
    column_names_cn = []
    column_names_en = []
    for i in iter(data_list):
        column_names_cn.append(i['COLUMN_COMMENT'])
        column_names_en.append(i['COLUMN_NAME'])
    return jsonify(column_names_cn)


# todo 文件服务   excel xls 生成下载   上传
# @app.route("/table/download", methods=["GET", "POST"])
# def download_xls_standard():
#     return "download"

# columns_names_en = ['ship_id', 'ship_name', 'ship_registration_number', 'port_of_registry', 'call_sign',
#                     'ship_type', 'tonnage', 'total_power']

@app.route("/table/default_format", methods=['GET'])
@siwa.doc(tags=['table'])
def download_default_format():
    sql_c = "select COLUMN_NAME,COLUMN_COMMENT from information_schema.columns WHERE table_name='ship_info' and table_schema='my_db01' order by ordinal_position"
    data_list = mysql_operate.db.select_db(sql_c)
    column_names_cn = []
    for i in iter(data_list):
        column_names_cn.append(i['COLUMN_COMMENT'])
        array_ = [column_names_cn]
    return flask_excel.make_response_from_array(array_, file_type='xlsx', file_name='船舶最低安全配员证书')


@app.route("/table/upload", methods=['POST'])
@siwa.doc(tags=['table'])
def upload_xls_file_data():
    if request.method == 'POST':
        upload_file = request.files['file']
        records = pyexcel.get_records(file_content=upload_file.stream, sheet_name='pyexcel_sheet1')

        # columns_names_cn = ['船舶识别号', '船名', '船舶登记号码', '船籍港', '呼号', '船舶种类', '总吨', '总机功率',
        #                     '机舱自动化程度', '载客限额', '航区', '船长', '大副', '二副', '三副', '驾驶员',
        #                     '高级值班水手', '值班水手', '轮机长', '大管轮', '二管轮', '三管轮', '轮机员', '高级值班机工',
        #                     '值班机工', '客运部人员', '兼职GMDSS限用操作员', 'GMDSS通用操作员', '电子电气员', '电子技工',
        #                     '专职GMDSS无线电电子员']

        for record in records:
            ship = ShipInfo(
                ship_id=record['船舶识别号'],
                ship_name=record['船名'],
                ship_registration_number=record['船舶登记号码'],
                port_of_registry=record['船籍港'],
                call_sign=record['呼号'],
                ship_type=record['船舶种类'],
                tonnage=record['总吨'],
                total_power=record['总机功率'],
                degree_of_cabin_automation=record['机舱自动化程度'],
                passenger_capacity_quota=record['载客限额'],
                navigation_zone=record['航区'],
                captain=record['船长'],
                first_mate=record['大副'],
                second_mate=record['二副'],
                third_mate=record['三副'],
                pilot=record['驾驶员'],
                senior_duty_sailor=record['高级值班水手'],
                duty_sailor=record['值班水手'],
                chief_engineer=record['轮机长'],
                first_engineer=record['大管轮'],
                second_engineer=record['二管轮'],
                third_engineer=record['三管轮'],
                marine_engineer=record['轮机员'],
                senior_duty_mechanic=record['高级值班机工'],
                duty_mechanic=record['值班机工'],
                passenger_transport_department_personnel=record['客运部人员'],
                part_time_GMDSS_restricted_operator=record['兼职GMDSS限用操作员'],
                GMDSS_No=record['GMDSS通用操作员'],
                electronic_electrical_technician=record['电子电气员'],
                electronics_technician=record['电子技工'],
                full_time_radio_electronics_officer=record['专职GMDSS无线电电子员'],
            )
            db.session.add(ship)
            db.session.commit()

    return 'Excel data uploaded successfully and saved to MySQL database.'


@app.route("/table/export_default", methods=['GET', 'POST'])
@siwa.doc(tags=['table'])
def export_xls_ship_info():
    sql_c = "select COLUMN_NAME,COLUMN_COMMENT from information_schema.columns WHERE table_name='ship_info' and table_schema='my_db01' order by ordinal_position"
    # 获取字段名称和字段注释
    data_list = mysql_operate.db.select_db(sql_c)
    column_names_cn = []
    column_names_en = []
    for i in iter(data_list):
        column_names_cn.append(i['COLUMN_COMMENT'])
        column_names_en.append(i['COLUMN_NAME'])
    all_ship_info = mysql_operate.db.select_db("select * from ship_info limit 500")
    array_ = [column_names_cn]
    for i in all_ship_info:
        temp = []
        for j in range(len(column_names_en)):
            temp.append(i[column_names_en[j]])
        # temp.append(i['ship_id'])
        # temp.append(i['ship_name'])
        # temp.append(i['ship_registration_number'])
        # temp.append(i['port_of_registry'])
        # temp.append(i['call_sign'])
        # temp.append(i['ship_type'])
        # temp.append(i['tonnage'])
        # temp.append(i['total_power'])
        # temp.append(i['degree_of_cabin_automation'])
        # temp.append(i['passenger_capacity_quota'])
        # temp.append(i['navigation_zone'])
        # temp.append(i['captain'])
        # temp.append(i['first_mate'])
        # temp.append(i['second_mate'])
        # temp.append(i['third_mate'])
        # temp.append(i['pilot'])
        # temp.append(i['senior_duty_sailor'])
        # temp.append(i['duty_sailor'])
        # temp.append(i['chief_engineer'])
        # temp.append(i['first_engineer'])
        # temp.append(i['first_engineer'])
        # temp.append(i['second_engineer'])
        # temp.append(i['third_engineer'])
        # temp.append(i['marine_engineer'])
        # temp.append(i['senior_duty_mechanic'])
        # temp.append(i['duty_mechanic'])
        # temp.append(i['passenger_transport_department_personnel'])
        # temp.append(i['part_time_GMDSS_restricted_operator'])
        # temp.append(i['GMDSS_No'])
        # temp.append(i['electronic_electrical_technician'])
        # temp.append(i['electronics_technician'])
        # temp.append(i['full_time_radio_electronics_officer'])
        array_.append(temp)
        # print(temp)

    return flask_excel.make_response_from_array(array_, file_type='xlsx', file_name='船舶信息表')


if __name__ == '__main__':
    flask_excel.init_excel(app)
    # WARNING: This is a development server.
    # Do not use it in a production deployment. Use a production WSGI server instead.
    app.run(debug=True, host='0.0.0.0', port=9003, threaded=True)

    # Waitress 是一个简单而高效的 WSGI（Web Server Gateway Interface）服务器，它专注于提供可靠的、高性能的 Web 服务。
    # serve(app, host='0.0.0.0', port=10029, url_scheme='https', threads=4)
    # waitress-serve --host=0.0.0.0 --port=10029 app:app

# flask默认是没有开启debug模式的，开启debug模式，可以帮助我们查找代码里面的错误
# host = '127.0.0.1' 表示设置的ip，如果需要连接手机等设备，可以将手机和电脑连接同一个热点，将host设置成对应的ip
# port 为端口号，可自行设置
# threading 多线程处理
